#-*- coding: UTF-8 -*-
__author__ = 'Administrator'
import pymysql,sys,os
class sqlAnalytics:
    def __init__(self,filepath,output=None,host='localhost',user='root',password='',db='mysql'):
        if not os.path.isfile(filepath):
            print 'file not exist'
            print sys.exit()
        self.slowQuery = []
        self.errorQuery = []
        self.host = host
        self.user = user
        self.password = password
        self.db = db
        with open(filepath,'r') as f:
            self.sql = f.readlines()
        self.parse()
        if output==None:
            print "query without using index"
            for i in self.slowQuery:
                print i
            print 'error query'
            for v in self.errorQuery:
                print v
        else:
            if not os.path.isfile(output):
                print 'output file not exist'
                sys.exit()
            with open(output,'a') as o:
                o.write('query without using index')
                o.write(os.linesep)
                o.writelines(self.slowQuery)
                o.write(os.linesep)
                o.write(os.linesep)
                o.write(os.linesep)
                o.write('error query')
                o.write(os.linesep)
                o.writelines(self.errorQuery)



    def parse(self):
        for l in self.sql:
            if l[:6].lower() == 'select':
                sql = l.lower()
                conn = pymysql.connect(host=self.host,user=self.user,password=self.password,db=self.db, use_unicode=True, charset="utf8")
                cursor = conn.cursor()
                sql = 'explain '+sql
                try:
                    cursor.execute(sql)
                    result = cursor.fetchone()
                except:
                    self.errorQuery.append(l)
                    continue
                if result[5] is None:
                    l = l+'rows scanned: '+str(result[8])+os.linesep
                    self.slowQuery.append(l)


if __name__=='__main__':
    length = len(sys.argv)
    if length==1:
        print 'incorrect param format'
        sys.exit()
    elif length==2:
        sqlAnalytics(sys.argv[1])
    elif length==3:
        sqlAnalytics(sys.argv[1],sys.argv[2])
    elif length==7:
        sqlAnalytics(sys.argv[1],sys.argv[2],sys.argv[3],sys.argv[4],sys.argv[5],sys.argv[6])
    else:
        print 'invalid input parameter'
